﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using WMSADBUtility;
using System.Data.SqlClient;

namespace WMSADL.SY
{
    public class PublicDataSetDAL
    {
        public void ExecuteSQL(string strWhere)
        {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strWhere))
            {
                DataSet ds = new DataSet();
                db.ExecuteNonQuery(dbCommand);
            }
        }

        public DataSet GetDataSet(string strWhere)
        {
            return SYDZ.WMS.DBUtility.DbHelperSQL.Query(strWhere);
        }
        public DataSet GetDataSet(string tableName, string strWhere)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT * FROM " + tableName + " ");
            if (strWhere != "")
            {
                strSQL.Append(" WHERE " + strWhere + " ");
            }
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSQL.ToString()))
            {
                DataSet ds = new DataSet();
                return db.ExecuteDataSet(dbCommand);
            }
        }
        public DataSet GetDataSet(string strfile, string tableName, string strWhere)
        {
            StringBuilder strSQL = new StringBuilder();
            if (strfile == "")
            {
                strfile = "*";
            }
            strSQL.Append("SELECT " + strfile + " FROM " + tableName + " ");
            if (strWhere != "")
            {
                strSQL.Append(" WHERE " + strWhere + " ");
            }
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSQL.ToString()))
            {
                DataSet ds = new DataSet();
                return db.ExecuteDataSet(dbCommand);
            }
        }
        /// <summary>
        /// 拣货单
        /// </summary>
        public bool DataTableToSQLPickingList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_PickingList";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("PickNO", "PickNO");
                        bulkCopy.ColumnMappings.Add("ProductModel", "ProductModel");
                        bulkCopy.ColumnMappings.Add("ProductNO", "ProductNO");
                        bulkCopy.ColumnMappings.Add("Line", "Line");
                        bulkCopy.ColumnMappings.Add("NeedQty", "NeedQty");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.ColumnMappings.Add("PickQty", "PickQty");
                        bulkCopy.ColumnMappings.Add("Stauts", "Stauts");
                        bulkCopy.ColumnMappings.Add("UserCode", "UserCode");
                        bulkCopy.ColumnMappings.Add("UserName", "UserName");
                        bulkCopy.ColumnMappings.Add("PID", "PID");
                        bulkCopy.ColumnMappings.Add("PO", "PO");
                        bulkCopy.ColumnMappings.Add("CreatTime", "CreatTime");

                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
        public bool DataTableToSQLPickingASPList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_PickingListSAP";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("PickNO", "PickNO");
                        bulkCopy.ColumnMappings.Add("ProductModel", "ProductModel");
                        bulkCopy.ColumnMappings.Add("ProductNO", "ProductNO");
                        bulkCopy.ColumnMappings.Add("Line", "Line");
                        bulkCopy.ColumnMappings.Add("NeedQty", "NeedQty");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.ColumnMappings.Add("PickQty", "PickQty");
                        bulkCopy.ColumnMappings.Add("Stauts", "Stauts");
                        bulkCopy.ColumnMappings.Add("UserCode", "UserCode");
                        bulkCopy.ColumnMappings.Add("UserName", "UserName");
                        bulkCopy.ColumnMappings.Add("PID", "PID");
                        bulkCopy.ColumnMappings.Add("PO", "PO");
                        bulkCopy.ColumnMappings.Add("CreatTime", "CreatTime");

                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }

        /// <summary>
        /// 用户
        /// </summary>
        public bool DataTableToSQLUserList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "TUsers";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("Organization_id", "Organization_id");
                        bulkCopy.ColumnMappings.Add("Login_name", "Login_name");
                        bulkCopy.ColumnMappings.Add("Username", "Username");
                        bulkCopy.ColumnMappings.Add("password", "password");
                        bulkCopy.ColumnMappings.Add("Creat_time", "Creat_time");
                        bulkCopy.ColumnMappings.Add("Last_login_time", "Last_login_time");
                        bulkCopy.ColumnMappings.Add("EnableStatus", "EnableStatus");
                        bulkCopy.ColumnMappings.Add("LoginStatus", "LoginStatus");

                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
        public bool DataTableToSQLUserSAPList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "TUsersSAP";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("Organization_id", "Organization_id");
                        bulkCopy.ColumnMappings.Add("Login_name", "Login_name");
                        bulkCopy.ColumnMappings.Add("Username", "Username");
                        bulkCopy.ColumnMappings.Add("password", "password");
                        bulkCopy.ColumnMappings.Add("Creat_time", "Creat_time");
                        bulkCopy.ColumnMappings.Add("Last_login_time", "Last_login_time");
                        bulkCopy.ColumnMappings.Add("EnableStatus", "EnableStatus");
                        bulkCopy.ColumnMappings.Add("LoginStatus", "LoginStatus");

                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }

        /// <summary>
        /// 仓位
        /// </summary>
        public bool DataTableToSQLPositionList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_Position";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("WarehouseNO", "WarehouseNO");
                        bulkCopy.ColumnMappings.Add("AreaNO", "AreaNO");
                        bulkCopy.ColumnMappings.Add("PositionNO", "PositionNO");
                        bulkCopy.ColumnMappings.Add("PositionName", "PositionName");
                        bulkCopy.ColumnMappings.Add("Stauts", "Stauts");
                        bulkCopy.ColumnMappings.Add("Designated_Stauts", "Designated_Stauts");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
        public bool DataTableToSQLPositionSAPList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_PositionSAP";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("WarehouseNO", "WarehouseNO");
                        bulkCopy.ColumnMappings.Add("AreaNO", "AreaNO");
                        bulkCopy.ColumnMappings.Add("PositionNO", "PositionNO");
                        bulkCopy.ColumnMappings.Add("PositionName", "PositionName");
                        bulkCopy.ColumnMappings.Add("Stauts", "Stauts");
                        bulkCopy.ColumnMappings.Add("Designated_Stauts", "Designated_Stauts");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }

        /// <summary>
        /// 库存
        /// </summary>
        public bool DataTableToSQLStockList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_Stock";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("WarehouseNO", "WarehouseNO");
                        bulkCopy.ColumnMappings.Add("PositionNO", "PositionNO");
                        bulkCopy.ColumnMappings.Add("ProductNO", "ProductNO");
                        bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
                        bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
        public bool DataTableToSQLStockSAPList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_StockSAP";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("WarehouseNO", "WarehouseNO");
                        bulkCopy.ColumnMappings.Add("PositionNO", "PositionNO");
                        bulkCopy.ColumnMappings.Add("ProductNO", "ProductNO");
                        bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
                        bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }

        /// <summary>
        /// 库存
        /// </summary>
        public bool DataTableToSQLProductOrderList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_ProductOrder";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("GIT_NO", "GIT_NO");
                        bulkCopy.ColumnMappings.Add("VendorCode", "VendorCode");
                        bulkCopy.ColumnMappings.Add("PO_NO", "PO_NO");
                        bulkCopy.ColumnMappings.Add("Invoice_No", "Invoice_No");
                        bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
                        bulkCopy.ColumnMappings.Add("ProductNo", "ProductNo");
                        bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
                        bulkCopy.ColumnMappings.Add("Specification", "Specification");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.ColumnMappings.Add("QTY", "QTY");
                        bulkCopy.ColumnMappings.Add("Currency", "Currency");
                        bulkCopy.ColumnMappings.Add("Price", "Price");
                        bulkCopy.ColumnMappings.Add("Weight", "Weight");
                        bulkCopy.ColumnMappings.Add("HGSpecification", "HGSpecification");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
        public bool DataTableToSQLProductOrderASPList(DataTable dt)
        {
            bool falg = false;
            using (SqlConnection destinationConnection = new SqlConnection(Database.ConnString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "PD_ProductOrderASP";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        bulkCopy.ColumnMappings.Add("GIT_NO", "GIT_NO");
                        bulkCopy.ColumnMappings.Add("VendorCode", "VendorCode");
                        bulkCopy.ColumnMappings.Add("PO_NO", "PO_NO");
                        bulkCopy.ColumnMappings.Add("Invoice_No", "Invoice_No");
                        bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
                        bulkCopy.ColumnMappings.Add("ProductNo", "ProductNo");
                        bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
                        bulkCopy.ColumnMappings.Add("Specification", "Specification");
                        bulkCopy.ColumnMappings.Add("Unit", "Unit");
                        bulkCopy.ColumnMappings.Add("QTY", "QTY");
                        bulkCopy.ColumnMappings.Add("Currency", "Currency");
                        bulkCopy.ColumnMappings.Add("Price", "Price");
                        bulkCopy.ColumnMappings.Add("Weight", "Weight");
                        bulkCopy.ColumnMappings.Add("HGSpecification", "HGSpecification");
                        bulkCopy.WriteToServer(dt);
                        falg = true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                    }
                }
            }
            return falg;
        }
    }
}
